import models.connect_db as db
from flask import request,  jsonify
import models.preprocessing as pre
import models.geoMap as geoMap
import pandas as pd
import datetime
from dateutil.relativedelta import relativedelta


def city():
    sql = "select * from city"
    data = db.get_conn(sql)

    return jsonify({'city': data})


def get_cluster_relation():
    sql_str = "select * from city_all where date between '2013-01-01' and '2013-03-31';"
    req = db.excute_query(sql_str)
    columns = req[0].keys()
    data = pd.DataFrame(columns=columns, data=req).groupby('name')

    sql_str = "select * from cluster_quarter where date='2013-1' and main_index='AQI';"
    clusterData = db.excute_query(sql_str)
    length = len(clusterData)

    cluster_length = [0 for i in range(14)]
    clusterDict = [[] for i in range(14)]
    for i in range(length):
        clusterDict[int(clusterData[i]['category'])] += data.get_group(clusterData[i]['city_name']).to_dict('records')
        cluster_length[int(clusterData[i]['category'])] += 1
    relations = []
    for i in range(len(clusterDict)):
        relations.append(pre.cal_cluster_relation(clusterDict[i]))
    return relations, cluster_length


def dimension_sort():
    relations, cluster_length = get_cluster_relation()
    keys = relations[0].keys()
    sum_relation = {}
    for key in keys:
        this_sum = 0
        for i in range(len(relations)):
            this_sum += relations[i][key][0] * cluster_length[i] / 361
        sum_relation[key] = this_sum

    first_index = '风向'
    index_sorts = [first_index]
    while len(index_sorts) < 11:
        this_indexs = []
        for key in keys:
            if key.split('-')[0] != first_index:
                this_index = key.split('-')[0]
            else:
                this_index = key.split('-')[1]
            if key.find(first_index) != -1 and this_index not in index_sorts:
                this_indexs.append({
                    'name': this_index,
                    'value': sum_relation[key]
                })

        this_indexs = sorted(this_indexs, key=lambda x: x['value'], reverse=True)
        first_index = this_indexs[0]['name']
        index_sorts.append(first_index)
    return index_sorts


def pollution_iaqi():
    sql = "select PM25,PM10,SO2,NO2,CO,O3 from pollution_iaqi_2013 where date = %s"
    sql1 = "select AQI from city_all where date = %s"
    date1 ='2013-01-01'
    date = '20130101'
    aqi = db.get_conn(sql1, date1)
    dataList = []
    for s in aqi:
        dataList.append(s[0])
    data = db.get_conn(sql, date)
    v = pre.getIaqi(data)

    return jsonify({'V': v,'AQI': dataList})

def get_average(date,type):
    """
    计算每个城市主要指标平均值
    :param date:
    :param type:
    :param main_index:
    :return:
    """
    sql = 'select distinct name,lon,lat from city_all'
    citys = db.get_conn(sql)

    # for s in db.get_conn(sql):
    #     citys.append(s[0])
    if (type == 'week'):
        sql_str = "select * from city_all where date between '" + date + "' and DATE_ADD('" + date + "', INTERVAL 6 DAY)"
    if (type == 'month'):
        sql_str = "select * from city_all where date_format(date, '%%Y-%%m') = '" + date + "'"
    if (type == 'quarter'):
        sql_str = "select * from city_all where date_format(date, '%%Y-%%m')='" + date[0] + "' or date_format(date, '%%Y-%%m')='" + date[1] + "' or date_format(date, '%%Y-%%m')='" + date[2] + "'"
    req = db.excute_query(sql_str)
    columns = req[0].keys()
    data = pd.DataFrame(columns=columns, data=req).groupby('name')
    # values = []
    # keys = ['PM25(微克每立方米)', 'PM10(微克每立方米)', 'SO2(微克每立方米)', 'NO2(微克每立方米)', 'CO(毫克每立方米)', 'O3(微克每立方米)', 'RH(%)',
    #         'PSFC(Pa)', 'AQI', '风速', '风向', '摄氏温度']
    for item in citys:
        new_data = data.get_group(item[0]).to_dict('record')
        num = len(new_data)
        # dict(zip(keys, pre.cal_average(new_data, num)))
        # pre.cal_average(new_data, num)
        list = pre.cal_average(new_data, num)
        list.insert(0, item[0])
        list.insert(13, date)
        list.insert(14, item[1])
        list.insert(15, item[2])

        sql11 = "insert into city_week (city_name,`PM25(微克每立方米)`, `PM10(微克每立方米)`, `SO2(微克每立方米)`, `NO2(微克每立方米)`, `CO(毫克每立方米)`, `O3(微克每立方米)`, `RH(%%)`,`PSFC(Pa)`, AQI, `风速`, `风向`, `摄氏温度`,date,lon,lat) values "+str(tuple(list))

        db.get_conn(sql11)
        # values.append(tuple(list))

    return None

def get_average1(date,type):
    """
    计算每个城市iaqi平均值
    :param date:
    :param type:
    :param main_index:
    :return:
    """
    sql = 'select distinct city_name from pollution_iaqi'
    citys = []

    for s in db.get_conn(sql):
        citys.append(s[0])
    if (type == 'week'):
        sql_str = "select * from pollution_iaqi where date between '" + date + "' and DATE_ADD('" + date + "', INTERVAL 6 DAY)"
    if (type == 'month'):
        sql_str = "select * from pollution_iaqi where date_format(date, '%%Y-%%m') = '" + date + "'"
    if (type == 'quarter'):
        sql_str = "select * from pollution_iaqi where date_format(date, '%%Y-%%m')='" + date[0] + "' or date_format(date, '%%Y-%%m')='" + date[1] + "' or date_format(date, '%%Y-%%m')='" + date[2] + "'"
    req = db.excute_query(sql_str)
    columns = req[0].keys()
    data = pd.DataFrame(columns=columns, data=req).groupby('city_name')
    # values = []
    # keys = ['PM25(微克每立方米)', 'PM10(微克每立方米)', 'SO2(微克每立方米)', 'NO2(微克每立方米)', 'CO(毫克每立方米)', 'O3(微克每立方米)', 'RH(%)',
    #         'PSFC(Pa)', 'AQI', '风速', '风向', '摄氏温度']
    for item in citys:
        new_data = data.get_group(item).to_dict('record')
        num = len(new_data)
        # dict(zip(keys, pre.cal_average(new_data, num)))
        # pre.cal_average(new_data, num)
        list = pre.cal_average1(new_data, num)
        list.insert(0, item)
        list.insert(8, date)

        sql11 = "insert into pollution_iaqi_month (city_name,PM25,PM10,SO2,NO2,CO,O3,date) values "+str(tuple(list))

        db.get_conn(sql11)
        # values.append(tuple(list))

    return None

def get_index_average():
    """
    计算每个城市的11指标的平均值
    :return:
    """
    mes = request.get_json()
    type = mes['size']
    date = mes['time']
    # sql = 'select distinct name from city_all'
    # citys = []
    # for s in db.get_conn(sql):
    #     citys.append(s[0])
    if (type == 'week'):
        sql_str = "select * from city_all where date between '" + date + "' and DATE_ADD('" + date + "', INTERVAL 6 DAY)"
    if (type == 'month'):
        sql_str = "select * from city_all where date_format(date, '%%Y-%%m') = '" + date + "'"
    if (type == 'quarter'):
        sql_str = "select * from city_all where date_format(date, '%%Y-%%m')='" + date[
            0] + "' or date_format(date, '%%Y-%%m')='" + date[1] + "' or date_format(date, '%%Y-%%m')='" + date[2] + "'"
    req = db.excute_query(sql_str)
    columns = req[0].keys()
    data = pd.DataFrame(columns=columns, data=req).groupby('name')
    values = []
    keys = ['PM25(微克每立方米)', 'PM10(微克每立方米)', 'SO2(微克每立方米)', 'NO2(微克每立方米)', 'CO(毫克每立方米)', 'O3(微克每立方米)', 'RH(%)',
            'PSFC(Pa)', 'AQI', '风速', '风向', '摄氏温度']
    city_name = data.size().index
    for item in city_name:
        new_data = data.get_group(item).to_dict('record')
        num = len(new_data)
        # dict(zip(keys, pre.cal_average(new_data, num)))
        values.append(dict(zip(keys, pre.cal_average(new_data, num))))
    res = dict(zip(city_name, values))

    return jsonify({'data': res})

def get_main_index_all_citys():
    """
    所有的城市主要指标的平均值
    :return:
    """
    mes = request.get_json()
    type = mes['size']
    date = mes['time']
    main_index = mes['main']
    sql1 = 'select distinct name from city_all'
    citys = []
    for s in db.get_conn(sql1):
        citys.append(s[0])
    if date=='2013-01-01' or date=='2013-01' or date[0]=='2013-01':
        res = get_average(date, type, main_index)
        rate = [0]*361
        rates = dict(zip(citys, rate))
    else:
        if (type == 'week'):
            date1 = datetime.datetime.strptime(date, '%Y-%m-%d').date() + datetime.timedelta(days=-7)
            sql = "select * from city_all where date between '" + str(date1) + "' and DATE_ADD('" + str(
                date1) + "', INTERVAL 6 DAY)"
            sql_str = "select * from city_all where date between '" + date + "' and DATE_ADD('" + date + "', INTERVAL 6 DAY)"
        if (type == 'month'):
            date1 = (datetime.datetime.strptime(date, '%Y-%m').date() - relativedelta(months=1)).strftime('%Y-%m')
            sql = "select * from city_all where date_format(date, '%%Y-%%m') = '" + str(date1) + "'"
            sql_str = "select * from city_all where date_format(date, '%%Y-%%m') = '" + date + "'"
        if (type == 'quarter'):
            date1 = []
            for item in date:
                date1.append(
                    (datetime.datetime.strptime(item, '%Y-%m').date() - relativedelta(months=3)).strftime('%Y-%m'))
            sql = "select * from city_all where date_format(date, '%%Y-%%m')='" + str(
                date1[0]) + "' or date_format(date, '%%Y-%%m')='" + str(
                date1[1]) + "' or date_format(date, '%%Y-%%m')='" + str(date1[2]) + "'"
            sql_str = "select * from city_all where date_format(date, '%%Y-%%m')='" + date[
                0] + "' or date_format(date, '%%Y-%%m')='" + date[1] + "' or date_format(date, '%%Y-%%m')='" + date[
                          2] + "'"
        req = db.excute_query(sql_str)
        columns = req[0].keys()
        data = pd.DataFrame(columns=columns, data=req).groupby('name')
        req1 = db.excute_query(sql)
        columns1 = req1[0].keys()
        data1 = pd.DataFrame(columns=columns1, data=req1).groupby('name')
        values = []
        keys = ['PM25(微克每立方米)', 'PM10(微克每立方米)', 'SO2(微克每立方米)', 'NO2(微克每立方米)', 'CO(毫克每立方米)', 'O3(微克每立方米)', 'RH(%)',
                'PSFC(Pa)', 'AQI', '风速', '风向', '摄氏温度']
        rate = []
        for item in citys:
            new_data = data.get_group(item).to_dict('record')
            new_data1 = data1.get_group(item).to_dict('record')
            num = len(new_data)
            index = dict(zip(keys, pre.cal_average(new_data, num)))[main_index]
            index1 = dict(zip(keys, pre.cal_average(new_data1, num)))[main_index]
            rate.append('%.2f%%' % (((index - index1) / index1) * 100))
            values.append(index)
        res = dict(zip(citys, values))
        rates = dict(zip(citys, rate))

    return jsonify({'main_index': res,'rate':rates})

def get_main_index_city_cluster():
    """
    城市群主要指标的平均值
    :return:
    """
    mes = request.get_json()
    startDate = mes['startDate']
    endDate = mes['endDate']
    type = mes['size']
    main_index = mes['main']
    city_cluster =[]
    sql1 = 'select city_cluster from base_cluster'
    for s in db.get_conn(sql1):
        city_cluster.append(s[0])

    if (type == 'week'):
        sql_str = "select * from cluster_week where date between '" + startDate + "' and '" + endDate + "'"
        sql = "select distinct date from cluster_week where date between '" + startDate + "' and '" + endDate + "'"
    if (type == 'month'):
        sql_str = "select * from cluster_month where date between '" + startDate + "' and '" + endDate + "'"
        sql = "select distinct date from cluster_month where date between '" + startDate + "' and '" + endDate + "'"
    if (type == 'quarter'):
        sql_str = "select * from cluster_quarter where date between '" + startDate + "' and '" + endDate + "'"
        sql = "select distinct date from cluster_quarter where date between '" + startDate + "' and '" + endDate + "'"
    times = []
    for s in db.get_conn(sql):
        times.append(s[0])
    for item in times:
        res = get_average(item, type, main_index)
        if (type == 'week'):
            sql_str = "select * from cluster_week where date= '" + item + "'"
        if (type == 'month'):
            sql_str = "select * from cluster_month where date between= '" + item + "'"
        if (type == 'quarter'):
            sql_str = "select * from cluster_quarter where date between= '" + item + "'"
        req = db.excute_query(sql_str)
        columns = req[0].keys()
        data = pd.DataFrame(columns=columns, data=req).groupby('category')
        value = []
        for i in range(len(city_cluster)):
            new_data = data.get_group(str(i)).to_dict('record')

            # num = len(new_data)

    return None

def get_week(date,type):
    for i in range(784897):
        res = get_average(date, type)
        date = str(datetime.datetime.strptime(date, '%Y-%m-%d').date() + datetime.timedelta(days=+7))
        i = i+7
    # get_average(date, type)
    return None

if __name__=="__main__":
    # relation, cluster = get_cluster_relation()
    # date = '201405'
    # type = 'month'
    # main_index = 'AQI'
    # cluster = cluster_results(date, type, main_index)
    # date = ['2013-01','2013-02','2013-03']
    date = '2013-01-01'
    type = 'week'
    # date1 = (datetime.datetime.strptime(date, '%Y-%m').date() + relativedelta(months=1)).strftime('%Y-%m')
    for i in range(784897):
        get_average(date, type)
        date1 = str(datetime.datetime.strptime(date, '%Y-%m-%d').date() + datetime.timedelta(days=+7))
        # date1 = str((datetime.datetime.strptime(date, '%Y-%m').date() + relativedelta(months=1)).strftime('%Y-%m'))
        # date = date1
        # date1 = []
        # for item in date:
        #     date1.append(str((datetime.datetime.strptime(item, '%Y-%m').date() + relativedelta(months=3)).strftime('%Y-%m')))
        date = date1

        # i = i + 7
    # get_week(date,type)
